package cn.sc.summer.core.util.file;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.sc.summer.exception.base.BaseException;
import cn.sc.summer.exception.exceptions.CommonException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.Min;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * 类名：excel表格工具类
 *
 * @author a-xin
 * @date 2:55 PM
 */
@Slf4j
@Component
public class PoiExcelUtil {

    /**
     * Excel其余说明
     */
    public static final List<String> OTHER_INSTRUCTIONS = Collections.unmodifiableList(new ArrayList<String>() {
        private static final long serialVersionUID = 1L;

        {
            add("填写说明：\n" +
                    "1:带*的为必填项，否则导入不成功！\n" +
                    "2:导入前请删除第三行示例，否则导入失败！！");
        }
    });

    /**
     * 获取excel表格数据
     *
     * @param file     文件流
     * @param startRow 开始解析行数
     * @param endRow   解析终止行数 为 -1 则表示解析所有数据
     * @param sheerAt  工作薄位置
     * @author a-xin
     * @date 2023/4/3 11:18
     */
    public static List<List<String>> getExcelVale(@NonNull MultipartFile file,
                                                  @NonNull Integer startRow,
                                                  @NonNull @Min(value = -1) Integer endRow,
                                                  @NonNull Integer sheerAt) {
        if (file.getSize() > 1024 * 500) {
            throw new BaseException("上传Excel表格大小不能超过500KB！");
        }
        FileUtil.checkFileType(file, ".xlsx");
        List<List<String>> resultList = new ArrayList<>();
        XSSFWorkbook workbook;
        try {
            workbook = new XSSFWorkbook(file.getInputStream());
        } catch (IOException e) {
            throw new BaseException("Excel表格异常！");
        }
        XSSFSheet sheetAt = workbook.getSheetAt(sheerAt);

        //获取表头列数进行单元格数据获取
        XSSFRow firstRow = sheetAt.getRow(0);
        short lastCellNum = firstRow.getLastCellNum();

        for (int i = startRow; i <= ((endRow == -1) ? sheetAt.getLastRowNum() : endRow); i++) {
            XSSFRow row = sheetAt.getRow(i);
            List<String> cellList = new ArrayList<>();
            if (ObjectUtil.isNotNull(row)) {
                for (int j = 0; j < lastCellNum; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (ObjectUtil.isNotNull(cell)) {
                        String cellValue = getCellValue(cell);
                        if (StrUtil.isNotEmpty(cellValue)) {
                            cellList.add(cellValue);
                        } else {
                            cellList.add(" ");
                        }
                    } else {
                        cellList.add(" ");
                    }
                }
                if (!cellList.stream().allMatch(StrUtil::isBlank)) {
                    resultList.add(cellList);
                }
            }
        }
        return resultList;
    }

    /**
     * 写入excel表头数据
     *
     * @param workbook        excel表格
     * @param sheet           工作簿
     * @param backgroundColor 背景色
     * @param frontSize       字体大小
     * @param frontName       自提名称
     * @param height          行高
     * @param dataList        数据集合
     */
    public static void writeExcelTitleData(XSSFWorkbook workbook,
                                           XSSFSheet sheet,
                                           java.awt.Color backgroundColor,
                                           Short frontSize,
                                           String frontName,
                                           Float height,
                                           List<String> dataList) {

        //单元格格式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 水平布局：居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直布局：居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景颜色信息，通过RGB颜色设置
        XSSFColor xssfColor = new XSSFColor(backgroundColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(xssfColor);

        XSSFFont font1 = workbook.createFont();
        font1.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
        font1.setFontHeightInPoints(frontSize);
        font1.setFontName(frontName);

        XSSFFont font2 = workbook.createFont();
        font2.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
        font2.setFontHeightInPoints(frontSize);
        font2.setFontName(frontName);

        //2.遍历数据，将数据插入到数据域中
        XSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(height);
        int colum = 0;
        for (String value : dataList) {
            XSSFCell cell = row.createCell(colum);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(value);
            if (value.endsWith("*")) {
                XSSFRichTextString richTextString = workbook.getCreationHelper().createRichTextString(value);
                richTextString.applyFont(0, value.length() - 1, font1);
                richTextString.applyFont(value.length() - 1, value.length(), font2);
                cell.setCellValue(richTextString);
            }
            colum = colum + 1;
        }
    }

    /**
     * 写入excel表格数据
     *
     * @param workbook   excel表格
     * @param sheet      工作簿
     * @param frontColor 字体颜色
     * @param frontSize  字体大小
     * @param frontName  字体名称
     * @param align      单元格格式居中
     * @param height     行高
     * @param rowNum     第几行
     * @param dataList   数据集合
     * @param bool       是否写入额外数据
     */
    public static void writeExcelData(XSSFWorkbook workbook,
                                      XSSFSheet sheet,
                                      java.awt.Color frontColor,
                                      Short frontSize,
                                      String frontName,
                                      HorizontalAlignment align,
                                      Float height,
                                      Integer rowNum,
                                      List<String> dataList,
                                      Boolean bool) {

        XSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(height);

        //单元格格式
        XSSFCellStyle explainStyle = workbook.createCellStyle();
        // 水平布局：居中
        explainStyle.setAlignment(align);
        // 垂直布局：居中
        explainStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFFont explainFont = workbook.createFont();
        explainFont.setColor(new XSSFColor(frontColor));
        explainFont.setFontHeightInPoints(frontSize);
        explainFont.setFontName(frontName);

        explainStyle.setFont(explainFont);

        if (bool) {
            XSSFCell cell = row.createCell(dataList.size());
            cell.setCellStyle(explainStyle);
            cell.setCellValue(OTHER_INSTRUCTIONS.get(0));
        }

        explainStyle.setBorderBottom(BorderStyle.THIN);
        explainStyle.setBorderLeft(BorderStyle.THIN);
        explainStyle.setBorderRight(BorderStyle.THIN);
        explainStyle.setBorderTop(BorderStyle.THIN);
        explainStyle.setWrapText(true);

        int colum = 0;
        for (String value : dataList) {
            XSSFCell explainCell = row.createCell(colum);
            explainCell.setCellStyle(explainStyle);
            explainCell.setCellValue(value);
            colum++;
        }

    }

    /**
     * 写入excel表格数据
     *
     * @param workbook   excel表格
     * @param sheet      工作簿
     * @param frontColor 字体颜色
     * @param frontSize  字体大小
     * @param frontName  字体名称
     * @param height     行高
     * @param startRow   开始行
     * @param dataList   数据集合
     */
    public static void writeExcelData(XSSFWorkbook workbook,
                                      XSSFSheet sheet,
                                      java.awt.Color frontColor,
                                      Short frontSize,
                                      String frontName,
                                      Float height,
                                      Integer startRow,
                                      List<List<String>> dataList) {
        XSSFCellStyle cellStyle = getCellStyle(workbook);
        XSSFFont explainFont = workbook.createFont();
        explainFont.setColor(new XSSFColor(frontColor));
        explainFont.setFontHeightInPoints(frontSize);
        explainFont.setFontName(frontName);
        cellStyle.setFont(explainFont);
        int valueIndex = 0;
        for (int i = startRow < 0 ? 0 : startRow; i < dataList.size() + (startRow < 0 ? 0 : startRow); i++) {
            XSSFRow row = sheet.createRow(startRow);
            row.setHeightInPoints(height);
            int colum = 0;
            List<String> values = dataList.get(valueIndex);
            for (String value : values) {
                XSSFCell explainCell = row.createCell(colum);
                explainCell.setCellStyle(cellStyle);
                explainCell.setCellValue(value);
                colum++;
            }
            valueIndex++;
        }
    }

    /**
     * excel添加数据验证-下拉框
     *
     * @param sheet     工作簿
     * @param validData 校验的数据
     * @param index     添加的列
     */
    public static void setDataValid(XSSFSheet sheet, String[] validData, Integer startRow, Integer endRow, Integer index) {
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(validData);
        //参数顺序：开始行、结束行、开始列、结束列
        CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, index, index);
        XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    /**
     * 将原表格信息写入response返回前端下载
     *
     * @param request  请求
     * @param response 返回体
     * @param fileName 文件名称
     * @param workbook excel表格内容
     * @throws IOException io异常
     */
    public static void writeExcelData(HttpServletRequest request, HttpServletResponse response, String fileName, XSSFWorkbook workbook) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        fileName = FileUtil.getFileName(fileName, request);
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
        response.setHeader("Content-Disposition", "attachment;filename= " + fileName);
        response.setContentType("application/msexcel");
        String origin = request.getHeader("Origin");
        response.addHeader("Access-Control-Allow-Origin", origin);
        workbook.write(outputStream);
    }

    /**
     * 获取当前单元格的值
     *
     * @param cell 单元网格
     * @return 单元网格内容
     */
    public static String getCellValue(Cell cell) {
        String cellValue = " ";
        if (ObjectUtil.isNull(cell)) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            // 数字
            case NUMERIC:
                cell.setCellType(CellType.STRING);
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            // 字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            // Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            // 故障
            case ERROR:
                // 空值
            case BLANK:
            default:
                cellValue = " ";
                break;
        }
        return cellValue;
    }

    /**
     * 校验导入excel表头合法性
     *
     * @param inputStream 文件流
     * @param titleList   表头list
     */
    public static void checkExcelTitle(InputStream inputStream, List<String> titleList) {
        List<String> excelTitle = getExcelTitle(inputStream);
        if (excelTitle.size() < titleList.size()) {
            throw new CommonException("导入模板不正确，请重新下载模板导入！");
        }
        if (excelTitle.size() > titleList.size()) {
            excelTitle = excelTitle.subList(0, titleList.size());
        }
        for (int i = 0; i < excelTitle.size(); i++) {
            if (!excelTitle.get(i).equals(titleList.get(i))) {
                throw new CommonException("导入模板不正确，请重新下载模板导入！");
            }
        }
    }

    /**
     * 获取excel表头信息
     *
     * @param inputStream 文件流
     * @return 表头数据信息
     */
    public static List<String> getExcelTitle(InputStream inputStream) {
        XSSFWorkbook workbook;
        try {
            workbook = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            throw new CommonException("Excel表格异常！", e);
        }
        XSSFSheet sheet = workbook.getSheetAt(0);
        List<String> cellList = new ArrayList<>();
        for (int i = 0; i < 1; i++) {
            XSSFRow row = sheet.getRow(i);
            if (ObjectUtil.isNotNull(row)) {

                for (Cell cell : row) {
                    if (ObjectUtil.isNotNull(cell)) {
                        String cellValue = getCellValue(cell);
                        if (StrUtil.isNotEmpty(cellValue)) {
                            cellList.add(String.valueOf(cellValue));
                        } else {
                            cellList.add(" ");
                        }
                    }
                }

            }
        }
        return cellList;
    }

    /**
     * 设置单元格格式边框和居中
     *
     * @param workbook 工作簿
     * @return 单元格格式
     */
    public static XSSFCellStyle getCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 水平布局：居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直布局：居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        // 设置为文本格式
        DataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));
        return cellStyle;
    }

    /**
     * 设置单元格格式居中，无边框
     *
     * @param workbook 工作簿
     * @return 单元格格式
     */
    public static XSSFCellStyle getCellStyleNoBorder(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // 水平布局：居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直布局：居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        // 设置为文本格式
        DataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));
        return cellStyle;
    }

    /**
     * 自动设置工作表列宽
     *
     * @param sheet 自动设置列宽的工作簿
     * @param row   需要设置的行数
     * @param size  放大倍数，一般在200-400
     */
    public static void autoColumnWidth(XSSFSheet sheet, Integer row, @NonNull Integer size) {
        //设置自动列宽配置
        int maxColumn = sheet.getRow(row).getPhysicalNumberOfCells();
        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    try {
                        int length = currentCell.toString().getBytes("GBK").length;
                        if (columnWidth < length + 1) {
                            columnWidth = length + 8;
                        }
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    }
                }
            }
            sheet.setColumnWidth(columnNum, Math.min(columnWidth, 50) * size);
        }
    }

}
